import sqlite3
import os

import Setting

class SQLiteManager():
    #I really wonder which mother fucker picked the word where as a SQL
    #table field
    create_list_sql = "(title text, start bigint(20), end bigint(20),\
place text, desc text, type text, repe tinyint(4), rmnd tinyint(4),\
prio tinyint(4), stat int, ct bigint(20), mt bigint(20))"

    create_record_sql = "create table record (type text, start bigint(20),\
end bigint(20), rating tinyint(4), mood tinyint(4), stat int,\
ct bigint(20), mt bigint(20))"
    create_sync_sql = "create table history (st bigint(20))"


    def __init__(self, db_path = 'default.db'):
        """
        set up db, create one if there's no one
        """
        exist = 0
        if os.path.exists(db_path):
           exist = 1 
        self._db = sqlite3.connect(db_path, check_same_thread=False)
        if exist == 0:
            self.setup()

    def setup(self):
        import ModelTypeList
        self._db.execute('create table wish ' + self.create_list_sql)
        self._db.execute('create table collect ' + self.create_list_sql)
        self._db.execute('create table todo ' + self.create_list_sql)
        self._db.execute(self.create_record_sql)
        self._db.execute(self.create_sync_sql)
        self._db.execute('insert into history values(0)')
        ModelTypeList.type_lists_setup(self._db)
        self._db.commit()
        
    def add(self, table_name, value):
        sql = "insert into %s values %s"%(table_name, value)
        #print("SQL:%s"%(sql))
        self._db.execute(sql)
        self._db.commit()

    def mark_delete(self, table_name, condition, time):
        #mark as deleted. It can only be deleted if sync with server has been done
        sql = "update %s set stat = -1, mt = %d where %s"%(table_name, time, condition)
        self._db.execute(sql)
        self._db.commit()
            
    def delete(self, table_name, condition):
        sql = "delete from %s where %s"%(table_name, condition)
        self._db.execute(sql)
        self._db.commit()

        
    def querry(self, sql):
        return self._db.execute(sql).fetchall()

    def after_sync_cleanup(self, table_name):
        self._db.execute('delete from %s where stat == -1'%(table_name))
        self._db.execute('update %s set stat = 0 where stat > 0'%(table_name))
        self._db.commit()
    
    def __del__(self):
        self._db.close()

if __name__ == '__main__':
    cx = SQLiteManager()
